#!/usr/bin/env python
# coding: utf-8

# In[86]:


# import pandas as pd 
# import pymysql

# data = pd.read_csv("../data/user_balance_table.csv")
# data


# In[2]:


# host = "localhost"
# user = "root"
# passwd = "392892"
# db = "course"
# from sqlalchemy import create_engine
# import pymysql
# engine = create_engine(f"mysql+pymysql://{user}:{passwd}@{host}/{db}?charset=utf8")
# data.to_sql("5_task",con=engine,if_exists="replace",index=False)


# In[3]:


import pandas as pd
import pymysql
import numpy as np


# 数据读取
host = "localhost"
user = "root"
passwd = "392892"
db = "course"
sql = "select * from 5_task;"
try:
    conn = pymysql.connect(host=host, user=user, passwd=passwd,db=db,charset="utf8")
    cursor = conn.cursor()
    print("数据库连接成功！")
    cursor.execute(sql)
    res = cursor.fetchall()
    print("SQL执行成功！")
    cols = [cursor.description[i][0] for i in range(len(cursor.description))]
    df = pd.DataFrame(res, columns=cols)
    print("完成！")
except Exception as e:
    print(e)


# In[41]:


df2 = df.copy()

# 选择用户资金流量数据
df2 = df2[["user_id", "report_date","tBalance", "yBalance"]]
user_info = df2["user_id"].value_counts()
user_ID = user_info.index
freq = user_info.values
df_user = pd.DataFrame(list(zip(user_ID, freq)), columns=["用户ID", "频次"])
df_user = df_user[df_user["频次"] > 400]  # 过滤掉出现频次低于阈值的客户。
ID = df_user["用户ID"][0]
df_s = df2[df2["user_id"] == ID].reset_index(drop=True)  # 选择想要预测的客户ID
df_s["report_date"] = pd.to_datetime(df_s["report_date"].map(str)) # 日期格式处理
df_s = df_s[["report_date","tBalance"]].drop_duplicates().sort_values(by="report_date", ascending=True, ignore_index=True).set_index("report_date")


# #### 平稳性检验

# In[83]:


# 折线图展示数据大致趋势
import statsmodels.api as sm
from statsmodels.stats.diagnostic import acorr_ljungbox
from statsmodels.graphics.tsaplots import plot_pacf,plot_acf
p1 = np.round(sm.tsa.stattools.adfuller(df_s)[1],5)
p2 = sm.tsa.stattools.adfuller(df_s.diff()[1:])[1]
from matplotlib import pyplot as plt
plt.rcParams["axes.unicode_minus"] = False
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.figure(figsize=(8,6),dpi=100)
plt.plot([i for i in df_s.index],df_s, "--g")
plt.plot([i for i in df_s.index],df_s.diff(), "-b")
plt.legend([f"原始数据\n平稳性检验P值：{p1}",f"一阶差分\n平稳性检验P值：{p2}"])
plt.title("原始数据与一阶差分平稳性对比\n（平稳性检验的P值远小于0.1，则表明拒绝非平稳性的原假设，数据是平稳的）")
plt.xlabel("时间")
plt.ylabel("资金")
# plt.show()
# plt.savefig("图片保存路径")


# #### 处理非平稳数据

# In[85]:


# （1）差分法
df_diff = df_s.diff()
df_diff

